# -*- coding: utf-8 -*-
# @Author  : ying.huang
# @Date    : 2024/3/17 22:55
import logging
import re
import requests  # pip3 install pymysql
import pymysql  # pip3 install pymysql
from six import itervalues  # pip3 install six -i https://pypi.tuna.tsinghua.edu.cn/simple/
import urllib3

urllib3.disable_warnings()


def get_headers(value):
    dic = {}
    if value:
        for line in value.splitlines():
            if "# " not in line:
                reg = re.match("^(.*?): (.*?)$", line)
                if reg:
                    dic[reg.group(1)] = reg.group(2)
    return dic


def retry(retry_num=3):
    def decorator(func):
        def wrapper(*args, **kw):
            att = 0
            while att < retry_num:
                try:
                    return func(*args, **kw)
                except Exception as e:
                    print("第{}次错误：{}, {}".format(att, func.__name__, str(e)))
                    att += 1
                    if att == retry_num:
                        print("已重试最大次数")

        return wrapper

    return decorator

#以下为获取数据代码
#若失败，多次尝试获取
@retry(retry_num=3)
def get_response(url, params):
    #模拟请求发送
    response = requests.post(url, data=params, headers=headers, verify=False)
    response.encoding = response.apparent_encoding
    #获得数据，json格式，不用处理，直接使用
    return response.json()


def escape(string):
    return '`%s`' % string


def insert_update_data_mysql(tablename, **value):
    connect = pymysql.Connect(**DBConfig)
    try:
        _key_values = ", ".join(["%s = %s" % (escape(k), "%s") for k in value])
        sql_query = "INSERT INTO %s SET %s ON DUPLICATE KEY UPDATE %s" % (tablename, _key_values, _key_values)
        with connect.cursor() as cur:
            cur.execute(sql_query, list(itervalues(value)) * 2)
        return cur
    except Exception as e:
        raise Exception("调用mysql插入方法时发生错误：{}".format(e))
    finally:
        connect.commit()
        connect.close()


def select_data_mysql(tablename, what='*', where="", where_values=[], order=None, offset=0, limit=None):
    connect = pymysql.Connect(**DBConfig)
    try:
        if isinstance(what, list) or isinstance(what, tuple) or what is None:
            what = ','.join(escape(f) for f in what) if what else '*'
        sql_query = "SELECT %s FROM %s" % (what, tablename)
        if where:
            sql_query += " WHERE {}".format(where)
        if order:
            sql_query += ' ORDER BY %s' % order
        if limit:
            sql_query += " LIMIT %d, %d" % (offset, limit)
        elif offset:
            sql_query += " LIMIT %d, %d" % (offset, -1)
        where_values = None if not where_values else where_values
        with connect.cursor() as cursor:
            cursor.execute(sql_query, where_values)
        return cursor
    except Exception as e:
        raise Exception("调用mysql插入方法时发生错误：{}".format(e))
    finally:
        connect.close()


header = '''
Accept:application/json, text/javascript, */*; q=0.01
Accept-Language:zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6
Connection:keep-alive
Content-Type:application/x-www-form-urlencoded; charset=UTF-8
Host:nlc.chinanutri.cn
Origin:https://nlc.chinanutri.cn
Referer:https://nlc.chinanutri.cn/fq/foodlist_0_0_0_0_0_1.htm
User-Agent:Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36 Edg/122.0.0.0
X-Requested-With:XMLHttpRequest
'''

headers = get_headers(header)

# 通过名字查询
search_url = 'https://nlc.chinanutri.cn/fq/FoodInfoQueryAction!queryFoodInfoList.do'
search_params = {
    'categoryOne': 0,
    'categoryTwo': 0,
    'foodName': '',  # 'foodName': '苹果',
    'pageNum': 1,  # 页数
    'field': 0,
    'flag': 0,
}

# 获取更多数据 / 详情
detail_url = 'https://nlc.chinanutri.cn/fq/foodinfo/{}.html'  # {} 中为食品 id

# 数据库配置
DBConfig = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "db": "mytest",
    "charset": "utf8mb4"
}

#
if __name__ == '__main__':
    # 获取请求数据（json格式）
    data = get_response(search_url, search_params)
    if data:
        # 下面为数据处理部分
        # 获取总页数
        total_pages = data['totalPages']
        # 获取所有页的数据
        for i in range(total_pages):
            search_params['pageNum'] = i + 1
            data = get_response(search_url, search_params)
            # 获取数据列表
            for item in data['list']:
                # 获取具体某一项数据
                result = {
                    'foodId': item[0],
                    'name': item[2],
                    'energy': item[7]
                }
                print('pageNum: {},data:{}'.format(i, result))
                #加入数据库中
                insert_update_data_mysql('food', **result)
